<?php
/*
 * AWS SES返回系统邮件发送情况到emp
 * @user liugang
 * @date 2018-3-26
 */
namespace app\controllers;
use Yii;
use app\base\Controller;

use app\components\redis\RedisLock;
use app\components\FileLock;
use app\models\Elog;
use app\components\Site;
use yii\mongodb\Query;
use yii\mongodb\ActiveRecord;


use app\models\DealTraceEmail;

class StatisticsSystemEmailController extends Controller
{
    public $enableCsrfValidation = false; 
    
    /*
     * 接收aws ses返回系统邮件发送情况
     * 
     */
    public function actionGetAwsReturnDatas()
    {
        // 接收get参数
        $get = Yii::$app->request->get();
        // 接收AWS SES传递的json数据
        $data = file_get_contents("php://input");
//        Elog::logCrontab($data, 'actionGetAwsReturnDatas','info');
//        $this->writeLog($data);
     
        $siteId = $get['site_id'] ? intval($get['site_id']) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($siteId && $siteName) {
            $receiveData = json_decode($data, 1);
            if(!$receiveData) {
                return;
            }
            // 提交订阅和取消订阅请求
            if(in_array($receiveData['Type'], ['SubscriptionConfirmation', 'UnsubscribeConfirmation'])) {
                
//                $msg = var_export($receiveData, 1);
//                $this->writeLog($msg);
                Elog::logCrontab($data, 'actionGetAwsReturnDatas','info');
                
            } else if($receiveData['Type'] == 'Notification') { // 消息通知
                $message = json_decode($receiveData['Message'], 1);
//                $this->writeLog(var_export($message, 1));
                // 是事件类型并且属于退回，投诉，拒绝
                if(isset($message['eventType']) && in_array($message['eventType'],['Bounce','Complaint','Reject'])) {
                    $dealTraceModel = new DealTraceEmail($siteName);
                    
                    if(!$dealTraceModel->getOne($receiveData['MessageId'])) {
                        // 不存在就写入数据
                        $dealTraceModel->message_id = trim($message['mail']['messageId']);
                        $dealTraceModel->email = trim($message['mail']['destination'][0]);
                        $dealTraceModel->notice_message_id = trim($receiveData['MessageId']);
                        if($message['eventType'] == 'Bounce') {
                            $dealTraceModel->notice_type = 1;
                            $dealTraceModel->bounceType = trim($message['bounce']['bounceType']);
                            $dealTraceModel->bounceSubType = trim($message['bounce']['bounceSubType']);
                            $dealTraceModel->bounceTime = Yii::$app->bjTime->strtotime($message['bounce']['timestamp']);
                        } else if($message['eventType'] == 'Complaint') {
                            $dealTraceModel->notice_type = 0;
                            $dealTraceModel->complaintType = isset($message['complaint']['complaintFeedbackType']) ? trim($message['complaint']['complaintFeedbackType']) : '';
                            $dealTraceModel->complaintTime = Yii::$app->bjTime->strtotime($message['complaint']['timestamp']);
                        } else if($message['eventType'] == 'Reject') {
                            $dealTraceModel->notice_type = 2;
                            $dealTraceModel->rejectReason = trim($message['reject']['reason']);
                        }

                        $dealTraceModel->add_time = yii::$app->bjTime->time();
                        
                        if(!$dealTraceModel->save()) {
                            $msg = "actionGetAwsReturnDatas操作保存AWS返回邮件发送失败数据入库失败，数据：".PHP_EOL.$data;
                            Elog::logCrontab($msg, 'actionGetAwsReturnDatas','exception');
                        }
                    }
                    
                    
                } else if (isset($message['eventType']) && $message['eventType'] == 'Delivery') {
                    // 接收AWS发送到达的数据并写入mongodb
                    $updateCondition = [
                        'message_id' => trim($message['mail']['messageId']),
                        'notice_id'  => trim($receiveData['MessageId'])
                    ];

                    // 需要更新的数据
                    $updateData = [
                        '$inc' => [
                            'count' => 1
                        ],
                        '$setOnInsert' => [
                            'send_start_time'   => Yii::$app->bjTime->strtotime($message['mail']['timestamp']),
                            'delivery_time' => Yii::$app->bjTime->strtotime($message['delivery']['timestamp']),
                            'process_time'  => intval($message['delivery']['processingTimeMillis']),
                            'add_time' => yii::$app->bjTime->time(),
                            'is_sync'  => 0
                        ]
                    ];

                    // 如果存在message_id和notice_id的记录就更新,否则就插入
                    try {
                        Yii::$app->mongodb1->getCollection('mail_send_'.$siteName)->update($updateCondition,$updateData,array('upsert' => true));
                    } catch (\Exception $e) {
                        $msg = 'actionGetAwsReturnDatas操作保存AWS返回邮件发送送达数据写入mongodb失败，失败信息：'.$e->getMessage().',数据：'.PHP_EOL.$data;
                        Elog::logCrontab($msg, 'actionGetAwsReturnDatas','exception');
                    }
                    
                    
                }
//                sleep(30);
            }
            
            
            
        } else {
            $msg = "actionGetAwsReturnDatas操作站点信息不存在，数据：".PHP_EOL.$data;
            Elog::logCrontab($msg, 'actionGetAwsReturnDatas','exception');
        }
 
         
        return 'success';
    }
    
    
    /*
     * 每五分钟将mongodb中的数据同步到MySQL
     * /statistics-system-email/sync-mongodb-to-mysql?site=sys_email&site_id=12
     */
    public function actionSyncMongodbToMysql()
    {
        ignore_user_abort(true); 
        $method = yii::$app->request->getMethod();
        
        $siteId = yii::$app->request->$method("site_id");
        
        $limit = yii::$app->request->$method("limit") ? yii::$app->request->$method("limit") : 2000;
        
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        if(!$siteName) {
            return '站点不存在';
        }
        
        $query = new Query;
        $datasArr = $query->from('mail_send_'.$siteName)->where(['is_sync'=>0])->orderBy('add_time')->limit($limit)->all(Yii::$app->mongodb1);
        $insertArr = $dataIdArr = [];
//        var_dump($datasArr);exit;
        
        $insertSql = 'insert into aws_send_'.$siteName.'(message_id,send_start_time,delivery_time,process_time) values';
        
        foreach($datasArr as $datas) {
            $dataIdArr[] = $datas['_id'];
            $insertArr[] = "('{$datas['message_id']}',{$datas['send_start_time']},{$datas['delivery_time']},{$datas['process_time']})";
        }
        
        if($insertArr) {
            $insertSql .= join(',',$insertArr);
            
            $trans = Yii::$app->db->beginTransaction(); // 开启事务
                
            try{
            
                Yii::$app->db->createCommand($insertSql)->execute(); 
                // 更改处理过的数据状态
                Yii::$app->mongodb1->getCollection('mail_send_'.$siteName)->update(array('_id' => $dataIdArr),array('is_sync' => 1));
                
                // 提交事务
                $trans->commit();
            } catch(\Exception $e) {
                // 事务回滚
                $trans->rollBack();
            }
        }
        return '数据同步成功！';
    }
    
    /*
     * 1.每天定时统计系统邮件数据到统计表
     * 2.将统计好的数据迁到另一张表中
     *
     */
    public function actionStatisticsSysData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $site_id = Yii::$app->request->get('site_id');
        $siteId = $site_id ? intval($site_id) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($site_id && $siteName) {
          
            $curDate = Yii::$app->bjTime->date('Y-m-d', Yii::$app->bjTime->time()); // 当前日期
            $curDayTime = Yii::$app->bjTime->strtotime($curDate);
//            $curDate = '2018-07-13'; // 当前日期
            
            // 从邮件数据表中统计每天每种邮件每种邮件类型的发送数据
            $sql = "select e.template_id,e.email_type,e.send_date,count(*) total_success_nums,sum(cast(a.delivery_time as signed)-cast(e.send_start_time as signed)) total_send_time from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.status = 1 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang = '' group by e.template_id,e.email_type,e.send_date";
            $sucessResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            // 从邮件数据表中统计每天每种邮件每种邮件类型的退回拒绝总数
            
            $sql = "select e.template_id,e.email_type,e.send_date,count(*) complaint_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 0 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang = '' group by e.template_id,e.email_type,e.send_date";
            $complaintResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $sql = "select e.template_id,e.email_type,e.send_date,count(*) bounce_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 1 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang = '' group by e.template_id,e.email_type,e.send_date";
            $bounceResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $sql = "select e.template_id,e.email_type,e.send_date,count(*) reject_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 2 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang = '' group by e.template_id,e.email_type,e.send_date";
            $rejectResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            
            
            $insertArr = $failArr = $complaintArr = $bounceArr = $rejectArr = [];
            
            foreach($complaintResult as $complaint) {
                $complaintArr[$complaint['template_id']][$complaint['email_type']][$complaint['send_date']] = $complaint['complaint_nums'];
            }
            
            foreach($bounceResult as $bounce) {
                $bounceArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']] = $bounce['bounce_nums'];
                if(isset($failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']])) {
                    $failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']] += $bounce['bounce_nums'];
                } else {
                    $failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']] = $bounce['bounce_nums'];
                }
            }
            
            foreach($rejectResult as $reject) {
                $rejectArr[$reject['template_id']][$reject['email_type']][$reject['send_date']] = $reject['reject_nums'];
                if(isset($failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']])) {
                    $failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']] += $reject['reject_nums'];
                } else {
                    $failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']] = $reject['reject_nums'];
                }
            }
            
            
            $insertSql = "insert into email_statistics(site_id,template_id,email_type,send_date,send_total_nums,send_success_nums,total_send_time_long,complaint_nums,bounce_nums,reject_nums) values";
            foreach($sucessResult as $success) {
                
                $failNums = isset($failArr[$success['template_id']][$success['email_type']][$success['send_date']]) ? $failArr[$success['template_id']][$success['email_type']][$success['send_date']] : 0;
                $complaintNums = isset($complaintArr[$success['template_id']][$success['email_type']][$success['send_date']]) ? $complaintArr[$success['template_id']][$success['email_type']][$success['send_date']] : 0;
                $bounceNums = isset($bounceArr[$success['template_id']][$success['email_type']][$success['send_date']]) ? $bounceArr[$success['template_id']][$success['email_type']][$success['send_date']] : 0;
                $rejectNums = isset($rejectArr[$success['template_id']][$success['email_type']][$success['send_date']]) ? $rejectArr[$success['template_id']][$success['email_type']][$success['send_date']] : 0;
                
                $insertArr[] = "($site_id,{$success['template_id']},{$success['email_type']},'{$success['send_date']}',{$success['total_success_nums']},".($success['total_success_nums']-$failNums).",".($success['total_send_time'] ? $success['total_send_time'] : 0).",$complaintNums,$bounceNums,$rejectNums)";
                
            }
            
            
            if($insertArr) {
                $insertSql .= join(',',$insertArr);
                
                $trans = Yii::$app->db->beginTransaction(); // 开启事务
                
                try{
                    
                    Yii::$app->db->createCommand($insertSql)->execute(); 
                    // 写入数据表成功后将明细数据清理出去
                    $moveSql = "insert into email_$siteName(site_id,user_id,template_uuqid,template_id,status,add_time,send_time_long,send_time,template_var,email,email_type,message_id,send_start_time,send_stop_time,aws_start_time,aws_stop_time,aws_process_time,pipelineCode,site_user_id,handle_email_id,send_date,lang)".
                            " select e.site_id,e.user_id,e.template_uuqid,e.template_id,e.status,e.add_time,e.send_time_long,e.send_time,e.template_var,e.email,e.email_type,e.message_id,e.send_start_time,e.send_stop_time,a.send_start_time,a.delivery_time,a.process_time,e.pipelineCode,e.site_user_id,e.id,e.send_date,e.lang from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.send_date <'{$curDate}' and e.send_date > 0 and e.lang = ''";
                    Yii::$app->db->createCommand($moveSql)->execute(); 
                        
                    // 清理成功后将原表数据清空
                    $delSql = "delete e,a from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.send_date <'{$curDate}' and e.add_time < $curDayTime and e.lang = ''";
                    
//                    $delSql = "delete from handle_email_$siteName where send_date <'{$curDate}'";
                    Yii::$app->db->createCommand($delSql)->execute(); 
                    
                    // 提交事务
                    $trans->commit();
                } catch(\Exception $e) {
                    // 事务回滚
                    $trans->rollBack();
                    
                    
                    echo $e->getMessage();
                }
            }
            
            return '数据处理完成！';
        } 
            
        return '站点信息不存在！';
    }
    
     /*
     * 1.每天定时统计系统邮件数据到统计表
     * 2.将统计好的数据迁到另一张表中
     *
     */
    public function actionNewStatisticsSysData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $site_id = Yii::$app->request->get('site_id');
        $siteId = $site_id ? intval($site_id) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($site_id && $siteName) {
          
            $curDate = Yii::$app->bjTime->date('Y-m-d', Yii::$app->bjTime->time()); // 当前日期
            
//            $curDate = '2018-09-12'; // 当前日期
            $curDayTime = Yii::$app->bjTime->strtotime($curDate);
            
            
            // 从邮件数据表中统计每天每种邮件每种邮件类型的发送数据
            $sql = "select e.template_id,e.email_type,e.send_date,e.pipelineCode,count(*) total_success_nums,sum(cast(a.delivery_time as signed)-cast(e.send_start_time as signed)) total_send_time from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.status = 1 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang != '' group by e.template_id,e.email_type,e.send_date,e.pipelineCode";
            $sucessResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            // 从邮件数据表中统计每天每种邮件每种邮件类型的退回拒绝总数
            
            $sql = "select e.template_id,e.email_type,e.send_date,e.pipelineCode,count(*) complaint_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 0 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang != '' group by e.template_id,e.email_type,e.send_date,e.pipelineCode";
            $complaintResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $sql = "select e.template_id,e.email_type,e.send_date,e.pipelineCode,count(*) bounce_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 1 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang != '' group by e.template_id,e.email_type,e.send_date,e.pipelineCode";
            $bounceResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $sql = "select e.template_id,e.email_type,e.send_date,e.pipelineCode,count(*) reject_nums from handle_email_$siteName e join deal_trace_email_$siteName d on e.message_id = d.message_id where d.notice_type = 2 and e.send_date < '{$curDate}' and e.send_date > 0 and e.lang != '' group by e.template_id,e.email_type,e.send_date,e.pipelineCode";
            $rejectResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            
            
            $insertArr = $failArr = $complaintArr = $bounceArr = $rejectArr = [];
            
            foreach($complaintResult as $complaint) {
                $complaintArr[$complaint['template_id']][$complaint['email_type']][$complaint['send_date']][$complaint['pipelineCode']] = $complaint['complaint_nums'];
            }
            
            foreach($bounceResult as $bounce) {
                $bounceArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']][$bounce['pipelineCode']] = $bounce['bounce_nums'];
                if(isset($failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']][$bounce['pipelineCode']])) {
                    $failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']][$bounce['pipelineCode']] += $bounce['bounce_nums'];
                } else {
                    $failArr[$bounce['template_id']][$bounce['email_type']][$bounce['send_date']][$bounce['pipelineCode']] = $bounce['bounce_nums'];
                }
            }
            
            foreach($rejectResult as $reject) {
                $rejectArr[$reject['template_id']][$reject['email_type']][$reject['send_date']][$reject['pipelineCode']] = $reject['reject_nums'];
                if(isset($failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']][$reject['pipelineCode']])) {
                    $failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']][$reject['pipelineCode']] += $reject['reject_nums'];
                } else {
                    $failArr[$reject['template_id']][$reject['email_type']][$reject['send_date']][$reject['pipelineCode']] = $reject['reject_nums'];
                }
            }
            
            
            $insertSql = "insert into email_statistics_new(site_id,template_id,pipeline_code,email_type,send_date,send_total_nums,send_success_nums,total_send_time_long,complaint_nums,bounce_nums,reject_nums) values";
            foreach($sucessResult as $success) {
                
                $failNums = isset($failArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']]) ? $failArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']] : 0;
                $complaintNums = isset($complaintArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']]) ? $complaintArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']] : 0;
                $bounceNums = isset($bounceArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']]) ? $bounceArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']] : 0;
                $rejectNums = isset($rejectArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']]) ? $rejectArr[$success['template_id']][$success['email_type']][$success['send_date']][$success['pipelineCode']] : 0;
                
                $insertArr[] = "($site_id,{$success['template_id']},'{$success['pipelineCode']}',{$success['email_type']},'{$success['send_date']}',{$success['total_success_nums']},".($success['total_success_nums']-$failNums).",".($success['total_send_time'] ? $success['total_send_time'] : 0).",$complaintNums,$bounceNums,$rejectNums)";
                
            }
            
            
            if($insertArr) {
                $insertSql .= join(',',$insertArr);
                
                $trans = Yii::$app->db->beginTransaction(); // 开启事务
                
                try{
                    
                    Yii::$app->db->createCommand($insertSql)->execute(); 
                    // 写入数据表成功后将明细数据清理出去
                    $moveSql = "insert into email_$siteName(site_id,user_id,template_uuqid,template_id,status,add_time,send_time_long,send_time,template_var,email,email_type,message_id,send_start_time,send_stop_time,aws_start_time,aws_stop_time,aws_process_time,pipelineCode,site_user_id,handle_email_id,send_date,lang)".
                            " select e.site_id,e.user_id,e.template_uuqid,e.template_id,e.status,e.add_time,e.send_time_long,e.send_time,e.template_var,e.email,e.email_type,e.message_id,e.send_start_time,e.send_stop_time,a.send_start_time,a.delivery_time,a.process_time,e.pipelineCode,e.site_user_id,e.id,e.send_date,e.lang from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.send_date <'{$curDate}' and e.send_date > 0 and e.lang != ''";
                    Yii::$app->db->createCommand($moveSql)->execute(); 
                    
                    $moveSql = "insert into email_{$siteName}_sub(handle_email_id,template_var,send_date,send_time)".
                            " select handle_email_id,template_var,send_date,send_time from handle_email_{$siteName}_sub where send_date <'{$curDate}' and send_date > 0";
                    Yii::$app->db->createCommand($moveSql)->execute(); 
                    
                    // 清理成功后将原表数据清空
                    $delSql = "delete e,a from handle_email_$siteName e left join aws_send_$siteName a on e.message_id = a.message_id where e.send_date <'{$curDate}' and e.add_time < $curDayTime and e.lang != ''";
                    Yii::$app->db->createCommand($delSql)->execute(); 
                    
                    $delSql = "delete from handle_email_{$siteName}_sub where send_date <'{$curDate}'";
                    Yii::$app->db->createCommand($delSql)->execute(); 
                    // 提交事务
                    $trans->commit();
                } catch(\Exception $e) {
                    // 事务回滚
                    $trans->rollBack();
                    
                    
                    echo $e->getMessage();
                }
            }
            
            return '数据处理完成！';
        } 
            
        return '站点信息不存在！';
    }
    
    public function actionStatisticsSysOpenData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $site_id = Yii::$app->request->get('site_id');
        $siteId = $site_id ? intval($site_id) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($site_id && $siteName) {
            
            // 
            $sql = "select e.site_id,e.template_id,e.email_type,e.send_date,count(*) open_count from email_$siteName e join email_{$siteName}_open_record o on e.handle_email_id = o.email_id where e.lang = '' group by e.template_id,e.email_type,e.send_date";
            $openResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            foreach($openResult as $open) {
                $updateSql = "update email_statistics set open_nums = {$open['open_count']} where site_id = {$open['site_id']} and template_id={$open['template_id']} and email_type={$open['email_type']} and send_date='{$open['send_date']}'";
                Yii::$app->db->createCommand($updateSql)->execute(); 
            }
            
            
            // 把打开数据统计到打开统计表中
            $sql = "select e.template_id,o.open_terminal,e.send_date,count(*) open_count from email_$siteName e join email_{$siteName}_open_record o on e.handle_email_id = o.email_id where e.lang = '' group by e.template_id,e.send_date,o.open_terminal";
            $openResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $insertArr = [];
            
            $insertSql = "insert into email_open_statistics(site_id,template_id,open_terminal,send_date,open_count,add_time) values";
            foreach($openResult as $open) {
                
                $insertArr[] = "($site_id,{$open['template_id']},{$open['open_terminal']},'{$open['send_date']}',{$open['open_count']},".Yii::$app->bjTime->time().")";
                
            }
            
            if($insertArr) {
                $insertSql .= join(',',$insertArr)." ON DUPLICATE KEY UPDATE open_count = values(open_count) ";
                
                Yii::$app->db->createCommand($insertSql)->execute(); 
            }
            
            
            return '数据处理完成！';
        }
        return '站点信息不存在！';
    }
    
    /*
     * 新版的统计打开脚本
     */
    public function actionNewStatisticsSysOpenData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $site_id = Yii::$app->request->get('site_id');
        $siteId = $site_id ? intval($site_id) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($site_id && $siteName) {
            // 
            $sql = "select e.site_id,e.template_id,e.email_type,e.send_date,e.pipelineCode,count(*) open_count from email_$siteName e join email_{$siteName}_open_record o on e.handle_email_id = o.email_id where e.lang != '' group by e.template_id,e.send_date,e.pipelineCode,e.email_type";
            $openResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            foreach($openResult as $open) {
                $updateSql = "update email_statistics_new set open_nums = {$open['open_count']} where site_id = {$open['site_id']} and template_id={$open['template_id']} and email_type={$open['email_type']} and pipeline_code='{$open['pipelineCode']}' and send_date='{$open['send_date']}'";
                Yii::$app->db->createCommand($updateSql)->execute(); 
            }
            
            
            // 把打开数据统计到打开统计表中
            $sql = "select e.template_id,o.open_terminal,e.send_date,e.pipelineCode,count(*) open_count from email_$siteName e join email_{$siteName}_open_record o on e.handle_email_id = o.email_id where e.lang != '' group by e.template_id,e.send_date,e.pipelineCode,o.open_terminal";
            $openResult = Yii::$app->db->createCommand($sql)->queryAll();
            
            $insertArr = [];
            
            $insertSql = "insert into email_open_statistics_new(site_id,template_id,open_terminal,pipeline_code,send_date,open_count,add_time) values";
            foreach($openResult as $open) {
                
                $insertArr[] = "($site_id,{$open['template_id']},{$open['open_terminal']},'{$open['pipelineCode']}','{$open['send_date']}',{$open['open_count']},".Yii::$app->bjTime->time().")";
                
            }
            
            if($insertArr) {
                $insertSql .= join(',',$insertArr)." ON DUPLICATE KEY UPDATE open_count = values(open_count) ";
                
                Yii::$app->db->createCommand($insertSql)->execute(); 
            }
            
            
            return '数据处理完成！';
        }
        return '站点信息不存在！';
    }
    
    /*
     * 手动将email_gearbest表中的template_var字段移除到email_gearbest_sub表中
     */
    public function actionMoveField()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $trans = Yii::$app->db->beginTransaction(); // 开启事务
                
        try{
        
            $moveSql = "insert into email_gearbest_sub(handle_email_id,template_var,send_date,send_time) select handle_email_id,template_var,send_date,send_time from email_gearbest where template_var !='' ";
            Yii::$app->db->createCommand($moveSql)->execute(); 
            $updateSql = "update email_gearbest set template_var='' where template_var !=''";
            Yii::$app->db->createCommand($updateSql)->execute(); 
            
            // 提交事务
            $trans->commit();
        } catch(\Exception $e) {
            // 事务回滚
            $trans->rollBack();


            echo $e->getMessage();
            exit;
        }
        
        echo "数据处理完成！";
    }
    /**
     * 每天将email_站点表的数据清理到其他表中，该表数据仅保存2个月数据
     */
    public function actionClearEmailSendData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $site_id = Yii::$app->request->get('site_id');
        $siteId = $site_id ? intval($site_id) : 0;
        $siteName = $siteId && isset(Yii::$app->params['sites']['sys_email']['web_index'][$siteId]) ? Yii::$app->params['sites']['sys_email']['web_index'][$siteId] : '';
        
        if($site_id && $siteName) {
          
            $curDate = Yii::$app->bjTime->date('Y-m-d', Yii::$app->bjTime->time()); // 当前日期
            
            $curDayTime = Yii::$app->bjTime->strtotime($curDate);
            
            $preDayTime = $curDayTime-60*24*3600;
            
            $trans = Yii::$app->db->beginTransaction(); // 开启事务
                
            try{

                // 写入数据表成功后将明细数据清理出去
                $moveSql = "insert into email_{$siteName}_copy(site_id,user_id,template_uuqid,template_id,status,add_time,send_time_long,send_time,template_var,email,email_type,message_id,send_start_time,send_stop_time,aws_start_time,aws_stop_time,aws_process_time,pipelineCode,site_user_id,handle_email_id,send_date,lang)".
                        " select site_id,user_id,template_uuqid,template_id,status,add_time,send_time_long,send_time,template_var,email,email_type,message_id,send_start_time,send_stop_time,aws_start_time,aws_stop_time,aws_process_time,pipelineCode,site_user_id,handle_email_id,send_date,lang from email_$siteName where send_time <{$preDayTime}";
                Yii::$app->db->createCommand($moveSql)->execute(); 
                
                $moveSql = "insert into email_{$siteName}_sub_copy(handle_email_id,template_var,send_date,send_time)".
                        " select handle_email_id,template_var,send_date,send_time from email_{$siteName}_sub where send_time <{$preDayTime}";
                Yii::$app->db->createCommand($moveSql)->execute(); 
                // 清理成功后将原表数据清空
                $delSql = "delete from email_$siteName where send_time <{$preDayTime}";
                Yii::$app->db->createCommand($delSql)->execute(); 
                
                $delSql = "delete from email_{$siteName}_sub where send_time <{$preDayTime}";
                Yii::$app->db->createCommand($delSql)->execute(); 
                // 提交事务
                $trans->commit();
            } catch(\Exception $e) {
                // 事务回滚
                $trans->rollBack();


                echo $e->getMessage();
            }
            echo '数据处理完成！';
        }
    }
    
    public function actionTest()
    {
        
        echo Yii::$app->bjTime->strtotime('2018-04-03T03:25:27.783Z');
        echo Yii::$app->bjTime->date('Y-m-d H:i:s',Yii::$app->bjTime->strtotime('2018-04-03T03:25:27.783Z'));
        
//        $data = json_encode(['ss'=>'5ss','ddd'=>'ffff']);
//        $url = "http://c.edmlocal.com/statistics-system-email/get-aws-return-datas?site=sys_email";
//            
//            $hearder = [
//                'Content-Type:application/json;charset=utf-8',
//                'Content-Length:'.strlen($data)
//            ];
//            $ch = curl_init();
//            // 设置URL和相应的选项
//            curl_setopt($ch, CURLOPT_URL, $url);
//            curl_setopt($ch, CURLOPT_HEADER, 0); 
//            curl_setopt($ch, CURLOPT_HTTPHEADER,$hearder);
//            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
//            curl_setopt($ch, CURLOPT_TIMEOUT, 10);
//            curl_setopt($ch, CURLOPT_POST, 1);
//            curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
//            // 抓取URL并把它传递给浏览器
//            $result = curl_exec($ch);
//            $return = curl_getinfo($ch);
//            //关闭cURL资源，并且释放系统资源
//            curl_close($ch);
//           var_dump($result,$return);
    }
    /**
     *
     * @param $msg
     */
    private function writeLog($msg)
    {
        $date = yii::$app->bjTime->date("Y-m-d H:i:s");
        $today = yii::$app->bjTime->date("Ymd");
        $dir = yii::$app->getRuntimePath() . "/logs/sysmqStatistices/" . yii::$app->bjTime->date("Ym");
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $filePath = $dir . "/sysmq_statistices_{$today}.log";
        $msg = "[{$date}] $msg" . PHP_EOL;
        file_put_contents($filePath, $msg, FILE_APPEND);
    }
    
}